package com.hunk.core.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * excel访问工具类
 * @author libo
 * @since 2010-08-19
 */
public class ExcelUtils {
	
	/**
	 * 获取表单sheet的第row行col列的单元格对象
	 * @param sheet
	 * @param row
	 * @param col
	 * @return
	 */
	public static HSSFCell getCell(HSSFSheet sheet, int row , int col){
		return sheet.getRow(row).getCell(col);
	}
	
	/**
	 * 获取xls中表单sheetName的row行col列的单元格
	 * @param filePath
	 * @param sheetName
	 * @param row
	 * @param col
	 * @return
	 */
	public static HSSFCell getCell(String filePath, String sheetName, int row , int col){
		HSSFSheet sheet = getSheet(filePath, sheetName);
		return sheet.getRow(row).getCell(col);
	}
	
	/**
	 * 获取xls中表单sheetName的row行col列的单元格
	 * @param is
	 * @param sheetName
	 * @param row
	 * @param col
	 * @return
	 */
	public static HSSFCell getCell(InputStream is, String sheetName, int row , int col){
		HSSFSheet sheet = getSheet(is, sheetName);
		return sheet.getRow(row).getCell(col);
	}
	

	/**
	 * 获取表单sheet的第row行col列的单元格的字符串值
	 * @param sheet
	 * @param row
	 * @param col
	 * @param isDate 单元格是否是日期类型的
	 * @return
	 */
	public static String getCellStrValue(HSSFSheet sheet, int row , int col, boolean isDate){
		HSSFCell cell = getCell(sheet, row , col);
		String result = null;
		result = getCellStrValue(cell,isDate);		
		return result;
	}
	
	/**
	 * 获取xls中表单sheetName的row行col列的单元格的字符串值
	 * @param filePath
	 * @param sheetName
	 * @param row
	 * @param col
	 * @param isDate
	 * @return
	 */
	public static String getCellStrValue(String filePath, String sheetName,int row , int col, boolean isDate){
		HSSFCell cell = getCell(filePath, sheetName, row , col);
		String result = null;
		result = getCellStrValue(cell,isDate);		
		return result;
	}
	/**
	 * 获取xls中表单sheetName的row行col列的单元格的字符串值
	 * @param is
	 * @param sheetName
	 * @param row
	 * @param col
	 * @param isDate
	 * @return
	 */
	public static String getCellStrValue(InputStream is, String sheetName,int row , int col, boolean isDate){
		HSSFCell cell = getCell(is, sheetName, row , col);
		String result = null;
		result = getCellStrValue(cell,isDate);		
		return result;
	}
	
	/**
	 * 获取单元格的字符串值
	 * @param cell
	 * @param isDateType 单元格是否是日期类型的
	 * @return
	 */
	public static String getCellStrValue(HSSFCell cell, boolean isDateType){
		if(cell != null){
			String result = null;
			if(isDateType){
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				result = sdf.format(cell.getDateCellValue());
			}else{
				switch (cell.getCellType()){
				case HSSFCell.CELL_TYPE_BOOLEAN:
					result = ((Boolean)cell.getBooleanCellValue()).toString();
					break;
				case HSSFCell.CELL_TYPE_STRING:
					result = cell.getStringCellValue();
					break;
				case HSSFCell.CELL_TYPE_NUMERIC:
					//解决科学计数法的问题
					NumberFormat nf = NumberFormat.getNumberInstance();
					nf.setMaximumFractionDigits(10);
					nf.setGroupingUsed(false);
					result = nf.format(cell.getNumericCellValue());
//					result = ((Double)cell.getNumericCellValue()).toString();
					break;
				case HSSFCell.CELL_TYPE_BLANK:
					result = "";
					break;
				case HSSFCell.CELL_TYPE_FORMULA:
					FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
					CellValue cellValue = evaluator.evaluate(cell);
					switch (cellValue.getCellType()) {
					case Cell.CELL_TYPE_BOOLEAN:
						result = ((Boolean)cellValue.getBooleanValue()).toString();
						break;
					case Cell.CELL_TYPE_NUMERIC:
						//解决科学计数法的问题
						nf = NumberFormat.getNumberInstance();
						nf.setMaximumFractionDigits(10);
						nf.setGroupingUsed(false);
						result = nf.format(cell.getNumericCellValue());
//						result = ((Double)cell.getNumericCellValue()).toString();
						break;
					case Cell.CELL_TYPE_STRING:
						result = cellValue.getStringValue();
						break;
					case Cell.CELL_TYPE_BLANK:
						result = "";
						break;
					case Cell.CELL_TYPE_ERROR:
						result = "Error here formula!";
						break;
						// CELL_TYPE_FORMULA will never happen
					case Cell.CELL_TYPE_FORMULA: 
						break;
					}				
					break;
				case HSSFCell.CELL_TYPE_ERROR:
					result = "Error here!";
					break;
				}
			}
			return result;
		}else{
			return "";
		}
	}
	
	
	
	/**
	 * 获取从startColNum列开始第rowNum行的单元格列表
	 * @param sheet
	 * @param startColNum
	 * @param rowNum
	 * @return
	 */
	public static List<HSSFCell> getRowCellsList(HSSFSheet sheet, int startColNum, int rowNum){
		List<HSSFCell> rowList = new ArrayList<HSSFCell>();
		HSSFCell cell = null;
		HSSFRow row = sheet.getRow(rowNum);
//		System.out.println("dsafds");
		if(row!=null){
			for(int i = startColNum;i<row.getLastCellNum();i++){
				cell = row.getCell(i);
				rowList.add(cell);
//			System.out.println(cell);
			}
		}
		return rowList;
	}
	
	/**
	 * 获取从startColNum列开始第rowNum行的单元格的值的列表
	 * @param sheet
	 * @param startColNum
	 * @param rowNum
	 * @return
	 */
	public static List<String> getRowCellsStrList(HSSFSheet sheet, int startColNum, int rowNum, int[] dateTypeColNum){
		List<String> rowStrList = new ArrayList<String>();
		List<HSSFCell> rowCellList = getRowCellsList(sheet, startColNum, rowNum);
		HSSFCell cell = null;
		for(int i=0; i<rowCellList.size(); i++){
			cell = rowCellList.get(i);
			if(Arrays.binarySearch(dateTypeColNum, cell.getColumnIndex()) != -1){
				rowStrList.add(getCellStrValue(cell, true));
			}else{				
				rowStrList.add(getCellStrValue(cell, false));
			}
		}
		return rowStrList;
	}
	/**
	 * 获取从startColNum列开始第rowNum行的单元格的值的列表
	 * @param filePath
	 * @param sheetName
	 * @param startColNum
	 * @param rowNum
	 * @param dateTypeColNum
	 * @return
	 */
	public static List<String> getRowCellsStrList(String filePath,String sheetName, int startColNum, int rowNum, int[] dateTypeColNum){
		HSSFSheet sheet = getSheet(filePath, sheetName);
		List<String> rowStrList = new ArrayList<String>();
		rowStrList = getRowCellsStrList(sheet, startColNum, rowNum, dateTypeColNum);
		return rowStrList;
	}
	
	/**
	 * 获取从startColNum列开始第rowNum行的单元格的值的列表
	 * @param is
	 * @param sheetName
	 * @param startColNum
	 * @param rowNum
	 * @param dateTypeColNum
	 * @return
	 */
	public static List<String> getRowCellsStrList(InputStream is,String sheetName, int startColNum, int rowNum, int[] dateTypeColNum){
		HSSFSheet sheet = getSheet(is, sheetName);
		List<String> rowStrList = new ArrayList<String>();
		rowStrList = getRowCellsStrList(sheet, startColNum, rowNum, dateTypeColNum);
		return rowStrList;
	}
	/**
	 * 获取从startRowNum行开始的第colNum列的单元格列表
	 * @param sheet
	 * @param startRowNum
	 * @param colNum
	 * @return
	 */
	public static List<HSSFCell> getColumnCellsList(HSSFSheet sheet, int startRowNum, int colNum){
		List<HSSFCell> colList = new ArrayList<HSSFCell>();
		HSSFCell cell = null;		
		int rowSize = sheet.getLastRowNum();
		HSSFRow row = null;
		for(int i = startRowNum; i <= rowSize; i++){
			row =   sheet.getRow(i);
			if(row!=null){
				cell = row.getCell(colNum);
				colList.add(cell);
			}
		}
		return colList;
	}
	
	/**
	 * 获取从startRowNum行开始的第colNum列的单元格的值
	 * @param sheet
	 * @param startRowNum
	 * @param colNum
	 * @param isDateType 该列数据是否是日期类型
	 * @return
	 */
	public static List<String> getColumnCellsStrList(HSSFSheet sheet, int startRowNum, int colNum, boolean isDateType){
		List<String> colList = new ArrayList<String>();
		List<HSSFCell> cellList = getColumnCellsList(sheet, startRowNum, colNum);
		for(HSSFCell cell : cellList){
			if(cell != null){
				colList.add(getCellStrValue(cell, isDateType));
			}
		}
		return colList;
	}
	/**
	 * 获取从startRowNum行开始的第colNum列的单元格的值
	 * @param sheet
	 * @param startRowNum
	 * @param colNum
	 * @param isDateType 该列数据是否是日期类型
	 * @return
	 */
	public static List<String> getColumnCellsStrList(String filePath, String sheetName, int startRowNum, int colNum, boolean isDateType){
		HSSFSheet sheet = getSheet(filePath, sheetName);
		List<String> colList = getColumnCellsStrList(sheet, startRowNum, colNum,isDateType);
		return colList;
	}
	
	/**
	 * 获取从startRowNum行开始的第colNum列的单元格的值
	 * @param is
	 * @param sheetName
	 * @param startRowNum
	 * @param colNum
	 * @param isDateType
	 * @return
	 */
	public static List<String> getColumnCellsStrList(InputStream is, String sheetName, int startRowNum, int colNum, boolean isDateType){
		HSSFSheet sheet = getSheet(is, sheetName);
		List<String> colList = getColumnCellsStrList(sheet, startRowNum, colNum,isDateType);
		return colList;
	}
	

	/**
	 * 获取HSSFWorkbook
	 * @param is
	 * @return
	 */
	public static HSSFWorkbook getWorkBook(InputStream is){
		HSSFWorkbook workBook = null;
		try {
			workBook = new HSSFWorkbook(is);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			if(is!=null){
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		
		return workBook;
	}
	
	/**
	 * 获取HSSFWorkbook
	 * @param filePath
	 * @return
	 */
	public static HSSFWorkbook getWorkBook(String filePath){
		FileInputStream fis = null;
		HSSFWorkbook workBook = null;
		try {
			fis = new FileInputStream(new File(filePath));
			workBook = new HSSFWorkbook(fis);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			if(fis!=null){
				try {
					fis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		
		return workBook;
	}
	/**
	 * 获取HSSFSheet
	 * @param filePath
	 * @param sheetName
	 * @return
	 */
	public static HSSFSheet getSheet(String filePath, String sheetName){
		HSSFWorkbook wb = getWorkBook(filePath);
		return wb.getSheet(sheetName);
	}
	
	/**
	 * 获取HSSFSheet
	 * @param is
	 * @param sheetName
	 * @return
	 */
	public static HSSFSheet getSheet(InputStream is, String sheetName){
		HSSFWorkbook wb = getWorkBook(is);
		return wb.getSheet(sheetName);
	}
	
	/**
	 * 设置合并单元格的样式
	 * @param fromRow
	 * @param toRow
	 * @param fromCol
	 * @param toCol
	 * @param cellStyle
	 */
//	public static void setMergedRegionStyle(int fromRow, int toRow,int fromCol,int toCol,HSSFSheet sheet,HSSFCellStyle cellStyle){
//		for(int i=fromRow;i<=toRow;i++){
//            HSSFRow row = sheet.getRow(i);
//            for(int j=fromCol;j<=toCol;j++){
//                HSSFCell cell = row.getCell(j);
//                if(cell ==null ){
//                    cell = row.createCell(j);
//                }
//                cell.setCellStyle(cellStyle);
//            }
//        }
//	}
	/**
	 * 设置合并单元格的样式
	 * @param sheet
	 * @param cra
	 * @param cellStyle
	 */
	public static void setMergedRegionStyle(HSSFSheet sheet, CellRangeAddress cra, HSSFCellStyle cellStyle){
		int rowFrom = cra.getFirstRow();
		int rowTo = cra.getLastRow();
		int colFrom = cra.getFirstColumn();
		int colTo = cra.getLastColumn();
		for(int i = rowFrom; i <= rowTo; i++){
			HSSFRow row = sheet.getRow(i);
			if(row == null){
				row = sheet.createRow(i);
			}
//			System.out.println("row");
			for(int j = colFrom; j <= colTo; j++){
//				System.out.println("cell");
				HSSFCell cell = row.getCell(j);
				if(cell == null){
					cell = row.createCell(j);
				}
				cell.setCellStyle(cellStyle);
			}
		}
	}
}
